package cn.ztydata.teachingsystem.heaven.dao;

import cn.ztydata.teachingsystem.common.Page;
import cn.ztydata.teachingsystem.heaven.common.WebContext;
import cn.ztydata.teachingsystem.heaven.cons.GroupType;
import cn.ztydata.teachingsystem.heaven.entity.Notice;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * 通知Dao类
 *
 * @author lyj
 * @since 2015-3-18
 */
@Repository
public class NoticeDao extends BaseDao {
    @Autowired
    private WebContext webContext;

    //<editor-fold desc="新增">
    /**
     * 新增通知
     *
     * @param notice 通知实体
     * @return int
     * @author lyj
     * @since 2015-3-18
     */
    public int addNotice(Notice notice){

        StringBuilder sql = new StringBuilder("INSERT INTO t_notice VALUES (NULL, :sTitle, :sContent, :iCreatorId, NOW(), :iGroupId, :tiGroupType)");

        MapSqlParameterSource param = new MapSqlParameterSource();

        param.addValue("iCreatorId", webContext.getUserId());
        param.addValue("sTitle", notice.getTitle());
        param.addValue("sContent", notice.getContent());
        param.addValue("iGroupId", notice.getGroupId());
        param.addValue("tiGroupType", notice.getGroupType());

        log.debug("新增通知");
        return namedParameterJdbcTemplate.update(sql.toString(), param);
    }
    //</editor-fold>

    //<editor-fold desc="删除">
    /**
     * 删除通知
     *
     * @param id 通知ID

     * @return int
     * @author lyj
     * @since 2015-3-18
     */
    public int deleteNotice(final long id){
        StringBuilder sql = new StringBuilder("DELETE FROM t_notice WHERE id = :id");

        log.debug("删除通知");
        return namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("id", id));
    }
    //</editor-fold>

    //<editor-fold desc="编辑">
    /**
     * 编辑通知
     *
     * @param notice 通知实体
     * @return int
     * @author lyj
     * @since 2015-3-18
     */
    public int editNotice(Notice notice) {
        StringBuilder sql = new StringBuilder("UPDATE t_notice SET sTitle = :sTitle, sContent = :sContent, dtCreate = NOW() WHERE id = :id");


        MapSqlParameterSource param = new MapSqlParameterSource()
                .addValue("id", notice.getId())
                .addValue("sTitle", notice.getTitle())
                .addValue("sContent", notice.getContent());

        log.debug("编辑通知");
        return namedParameterJdbcTemplate.update(sql.toString(), param);
    }
    //</editor-fold>

    //<editor-fold desc="查询">
    /**
     * 获取通知明细
     *
     * @param id 通知ID
     * @return Notice 通知实体
     * @author lyj
     * @since 2015-3-18
     */
    public Notice getNoticeDetail(final long id){

        StringBuilder sql = new StringBuilder("SELECT id,sTitle,sContent,dtCreate,iCreatorId,iGroupId,tiGroupType FROM t_notice WHERE id = :id");

        MapSqlParameterSource param = new MapSqlParameterSource().addValue("id", id);

        try {
            log.debug("获取通知明细");
            return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, new RowMapper<Notice>() {
                @Override
                public Notice mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Notice notice = new Notice();

                    notice.setId(rs.getLong("id"));
                    notice.setTitle(rs.getString("sTitle"));
                    notice.setContent(rs.getString("sContent"));
                    notice.setCreate(rs.getTimestamp("dtCreate"));
                    notice.setCreatorId(rs.getLong("iCreatorId"));
                    notice.setGroupId(rs.getLong("iGroupId"));
                    notice.setGroupType(rs.getInt("tiGroupType"));
                    return notice;
                }
            });
        }catch (EmptyResultDataAccessException ex){
            log.debug("获取通知明细失败");
            log.warn("获取通知明细失败：id->{}", id);

            return null;
        }
    }


    /**
     * 获取通知列表
     *
     * @param groupType 群组类型
     * @param groupId 群组ID
     * @param pageNum 当前页码
     * @param pageSize 每页条数
     * @return page
     * @author lyj
     * @since 2015-3-18
     */
    public Page<Notice> getNotices(final int groupType, final long groupId, final int pageNum, final int pageSize){
        StringBuilder sql = new StringBuilder("SELECT id,sTitle,dtCreate FROM t_notice");
        StringBuilder sqlCount = new StringBuilder("SELECT COUNT(*) FROM t_notice");

        StringBuilder where = new StringBuilder(" WHERE 1=1");

        MapSqlParameterSource param = new MapSqlParameterSource();

        //过滤群组类型
        if (groupType >= GroupType.NOT_GROUP && groupType <= GroupType.CLASS_GROUP){
            log.debug("过滤群组类型groupType >= GroupType.NOT_GROUP && groupType <= GroupType.CLASS_GROUP");
            where.append(" AND tiGroupType = :tiGroupType");
            param.addValue("tiGroupType", groupType);
        }

        //过滤群组ID
        if (groupId > 0){
            log.debug("过滤群组IDgroupId > 0");
            where.append(" AND iGroupId = :iGroupId");
            param.addValue("iGroupId", groupId);
        }

        //获取总条数
        int total = namedParameterJdbcTemplate.queryForObject(sqlCount.append(where).toString(), param, Integer.class);

        if (total == 0) {

            log.debug("total == 0");
            return new Page<>();
        }

        //追加排序
        where.append(" ORDER BY dtCreate DESC");

        //追加limit限制
        where.append(getLimitStr(pageNum, pageSize));

        List<Notice> noticeList = namedParameterJdbcTemplate.query(sql.append(where).toString(), param, new RowMapper<Notice>() {
            @Override
            public Notice mapRow(ResultSet rs, int rowNum) throws SQLException {
                Notice notice = new Notice();

                notice.setId(rs.getLong("id"));
                notice.setTitle(rs.getString("sTitle"));
                notice.setCreate(rs.getTimestamp("dtCreate"));

                return notice;
            }
        });

        log.debug("获取通知列表");
        return new Page<>(total, pageSize, pageNum, noticeList);

    }
    //</editor-fold>
}
